package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import javax.sql.RowSet;

/**
 * 农家乐店铺基本信息—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwfarmBasicinfo {

    /**
     * 农家乐信息列表查询
     */
    public JSONObject getfarminfolist(Integer pagesize, Integer pageindex, String starttime, String endtime,
                                      String searchval, String searchkey, String sortfild, String sortway,
                                      String type, String status,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.business_type         AS businessTypeid,\n" +
                "  a.farm_type             AS businessTypeName,\n" +
                "  b.business_name         AS business_name,\n" +
                "  b.business_CompanyName  AS CompanyName,\n" +
                "  b.business_Legal        AS Legal,\n" +
                "  b.business_tel          AS tel,\n" +
                "  a.business_encryptId    AS business_encryptId,\n" +
                "  a.farm_encryptId        AS farm_encryptId,\n" +
                "  a.farm_status           AS farm_status,\n" +
                "  a.farm_city             AS city,\n" +
                "  a.farm_District         AS district,\n" +
                "  a.farm_address          AS address,\n" +
                "  a.farm_consume          AS consume,\n" +
                "  a.farm_createtime       AS createtime\n" +
                "FROM xw_farm_basicinfo AS a\n" +
                "  LEFT JOIN xw_business_info b ON a.farm_encryptId = b.farm_encryptId\n" +
                "                                  AND a.business_encryptId = b.business_encryptId ";
        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //时间
        if (!starttime.equals("")) {
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and a.farm_createtime >= '" + starttime + "' and a.farm_createtime <= '" + endtime + "' ");
        }
        //店铺类型
        if (!type.equals("")) {
            sb.append(" and a.farm_type in('" + type + "') ");
        }
        //营业状态
        if (!status.equals("")) {
            sb.append(" and a.farm_status in('" + status + "') ");
        }

        //搜索内容查询
        if (!searchval.equals("")){
            if (searchval.equals("farm_consume")){
                searchval = "a.farm_consume";
            }else {
                searchval = "b."+searchval;
            }
            sb.append(" and ( "+searchkey+" like '%" +searchval + "%' )");
        }
        /**
         * 只显示已审核通过记录的商家记录
         * 0：未审核
         * 1：已通过
         * 2：已拒绝
         */
        sb.append(" and b.business_state in('1') ");
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by a." + sortfild + " " + sortway + " ");
        }

        try {

            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "SELECT\n" +
                    "  count(*)\n" +
                    "FROM xw_farm_basicinfo AS a\n" +
                    "  LEFT JOIN xw_business_info b ON a.farm_encryptId = b.farm_encryptId\n" +
                    "                                  AND a.business_encryptId = b.business_encryptId\n" +
                    "WHERE"+ sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("businessTypeid", (rs.getString("businessTypeid") == null ? "" : rs.getString("businessTypeid")));
                object.put("businessTypeName", (rs.getString("businessTypeName") == null ? "" : rs.getString("businessTypeName")));
                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("CompanyName", rs.getString("CompanyName"));
                object.put("Legal", rs.getString("Legal"));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("farm_encryptId", (rs.getString("farm_encryptId") == null ? "" : rs.getString("farm_encryptId")));
                object.put("farm_status", (rs.getString("farm_status") == null ? "" : rs.getString("farm_status")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("district", (rs.getString("district") == null ? "" : rs.getString("district")));
                object.put("address", (rs.getString("address")==null?"":rs.getString("address")));
                object.put("consume", (rs.getString("consume") == null ? "" : rs.getString("consume")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }

    /**
     * 商家详情——获取农家乐基本信息（一条）
     */
    public JSONObject getfarminfo(String business_encryptId, String farm_encryptId,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.business_encryptId    AS business_encryptId,\n" +
                "  a.farm_encryptId        AS farm_encryptId,\n" +
                "  b.business_name         AS business_name,\n" +
                "  a.farm_type             AS farmtypeName,\n" +
                "  b.business_tel          AS tel,\n" +
                "  b.business_Legal        AS business_Legal,\n" +
                "  b.business_CompanyName  AS CompanyName,\n" +
                "  b.business_create       AS business_create,\n" +
                "  b.business_license      AS business_license,\n" +
                "  b.business_Credit       AS business_Credit,\n" +
                "  a.farm_city             AS city,\n" +
                "  a.farm_District         AS district,\n" +
                "  a.farm_Business_area    AS farm_area,\n" +
                "  a.farm_status           AS farm_status,\n" +
                "  a.farm_address          AS farm_address,\n" +
                "  a.farm_Views            AS farm_views,\n" +
                "  a.farm_score            AS farm_score,\n" +
                "  a.farm_Dining_reception AS farm_dining,\n" +
                "  a.farm_scale            AS farm_scale,\n" +
                "  a.farm_consume          AS farm_consume,\n" +
                "  a.farm_room             AS farm_room,\n" +
                "  a.farm_createtime       AS createtime,\n" +
                "\n" +
                "  a.farm_palytype_desc AS  palytypeName,\n" +
                "  a.farm_playType_encryptId AS  palyType_encryptId,\n" +
                "  a.farm_bus_route        AS farm_bus_route,\n" +
                "  a.farm_Self_route       AS farm_self_route,\n" +
                "  a.farm_Intr_info        AS farm_Intr_info\n" +
                "FROM xw_farm_basicinfo a\n" +
                "  LEFT JOIN xw_business_info b ON a.business_encryptId = b.business_encryptId\n" +
                "                                  AND a.farm_encryptId = b.farm_encryptId ";
        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //查找某一个农家乐的基本信息
        if (!business_encryptId.equals("") && !farm_encryptId.equals("")) {
            sb.append(" and a.business_encryptId in('" + business_encryptId + "') ");
            sb.append(" and a.farm_encryptId in('" + farm_encryptId + "') ");
        }

        try {
            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            result.put("status", 0);
            result.put("msg", "ok");

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("farm_encryptId", (rs.getString("farm_encryptId") == null ? "" : rs.getString("farm_encryptId")));
                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("farmtypeName", (rs.getString("farmtypeName") == null ? "" : rs.getString("farmtypeName")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_Legal", (rs.getString("business_Legal") == null ? "" : rs.getString("business_Legal")));
                object.put("CompanyName", (rs.getString("CompanyName") == null ? "" : rs.getString("CompanyName")));
                object.put("business_create", (rs.getString("business_create") == null ? "" : rs.getString("business_create")));
                object.put("business_license", (rs.getString("business_license") == null ? "" : rs.getString("business_license")));
                object.put("business_Credit", (rs.getString("business_Credit") == null ? "" : rs.getString("business_Credit")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("district", (rs.getString("district") == null ? "" : rs.getString("district")));
                object.put("farm_area", (rs.getString("farm_area") == null ? "" : rs.getString("farm_area")));
                object.put("farm_status", (rs.getString("farm_status") == null ? "" : rs.getString("farm_status")));
                object.put("farm_address", (rs.getString("farm_address") == null ? "" : rs.getString("farm_address")));
                object.put("farm_views", (rs.getString("farm_views") == null ? "" : rs.getString("farm_views")));
                object.put("farm_score", (rs.getString("farm_score") == null ? "" : rs.getString("farm_score")));
                object.put("farm_dining", (rs.getString("farm_dining") == null ? "" : rs.getString("farm_dining")));
                object.put("farm_scale", (rs.getString("farm_scale") == null ? "" : rs.getString("farm_scale")));
                object.put("farm_consume", (rs.getString("farm_consume") == null ? "" : rs.getString("farm_consume")));
                object.put("farm_room", (rs.getString("farm_room") == null ? "" : rs.getString("farm_room")));

                object.put("palytypeName", (rs.getString("palytypeName") == null ? "" : rs.getString("palytypeName")));
                object.put("palyType_encryptId", (rs.getString("palyType_encryptId") == null ? "" : rs.getString("palyType_encryptId")));
                object.put("farm_bus_route", (rs.getString("farm_bus_route") == null ? "" : rs.getString("farm_bus_route")));
                object.put("farm_self_route", (rs.getString("farm_self_route") == null ? "" : rs.getString("farm_self_route")));
                object.put("farm_Intr_info", (rs.getString("farm_Intr_info") == null ? "" : rs.getString("farm_Intr_info")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);

        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }



}
